﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    /// <summary>
    /// SQL Server通用数据访问类
    /// </summary>
    public class SqlHelper
    {
        private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();

        #region 执行格式化sql语句
        /// <summary>
        /// 执行insert、delete、update操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行结果集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw ex;
            }
        }
        #endregion

        #region 执行带参数的sql语句和存储过程
        /// <summary>
        /// 执行insert、delete、update操作
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="isProcedure"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText,SqlParameter[] sqlParameters=null,bool isProcedure=false)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(cmdText, conn);

            if (sqlParameters != null)
            {
                cmd.Parameters.AddRange(sqlParameters);

            }
            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }

            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="isProcedure"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, SqlParameter[] sqlParameters = null, bool isProcedure = false)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(cmdText, conn);

            if (sqlParameters != null)
            {
                cmd.Parameters.AddRange(sqlParameters);

            }
            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }

            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行结果集查询
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="isProcedure"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string cmdText, SqlParameter[] sqlParameters = null, bool isProcedure = false)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(cmdText, conn);

            if (sqlParameters != null)
            {
                cmd.Parameters.AddRange(sqlParameters);

            }
            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }

            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                conn.Close();
                throw ex;
            }
        }
        #endregion

        #region 返回数据集查询（包括单张数据表以及多张数据表）

        #endregion

        #region 执行事务：提交多条不带参数的sql语句以及提交多条带参数的sql语句，适合主从表的关系

        #endregion

        #region 获取数据库服务器时间
        /// <summary>
        /// 获取服务器时间
        /// </summary>
        /// <returns></returns>
        public static DateTime GetServerTime()
        {
            return Convert.ToDateTime(ExecuteScalar("select getdate()"));
        }
        #endregion

        #region 日志记录
        /// <summary>
        /// 简单日志记录
        /// </summary>
        /// <param name="msg"></param>
        private static void WriteErrorLog(string msg)
        {
            FileStream fs = new FileStream("Error.log", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.Write("【" + GetServerTime() + "】:" + msg);
            sw.Close();
            fs.Close();
        }


        #endregion

    }
}
